package com.taomeng.dao.impl;

import com.github.pagehelper.Page;
import com.github.pagehelper.PageHelper;
import com.taomeng.dao.LeaveDao;
import com.taomeng.entity.LeaveApplication;
import com.taomeng.entity.LeaveApplicationDetail;
import com.taomeng.entity.LeaveApplicationInfo;
import com.taomeng.entity.PageBean;
import com.taomeng.util.JdbcTemplateSupport;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

@Repository
public class LeaveDaoImpl implements LeaveDao {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Autowired
    private JdbcTemplateSupport jdbcTemplateSupport;

    @Override
    public boolean add(LeaveApplication leaveApplication) {
        String sql = "insert into leave_application values(?,?,?,?,?,?,?)";
        Object[] params = {
                leaveApplication.getId(),
                leaveApplication.getStudentId(),
                leaveApplication.getType(),
                leaveApplication.getReason(),
                leaveApplication.getBegintime(),
                leaveApplication.getEndtime(),
                1
        };
        try {
            return jdbcTemplate.update(sql, params) == 1;
        } catch (DataAccessException e) {
            e.printStackTrace();
        }
        return false;
    }

    @Override
    public PageBean<LeaveApplicationInfo> findAllByPage(int page, int limit, String status, Long departmentId, Long userId) {
        StringBuilder sb = new StringBuilder("select " +
                "leave_application.id id, " +
                "student.name name, " +
                "student.num num, " +
                "student.sex sex, " +
                "class.name className, " +
                "t_user.name banZhuRenName, " +
                "leave_application.type leaveType, " +
                "leave_application.reason reason, " +
                "leave_application.begintime begintime, " +
                "leave_application.endtime endtime, " +
                "leave_application.`status` `status` " +
                "from " +
                "leave_application " +                          //leave_application
                "left join student " +                          //student
                "on leave_application.studentId=student.id  " +
                "left join class  " +                            //class
                "on student.classId=class.id " +
                "left join t_user " +                             //t_user
                "on class.banZhuRenId=t_user.id " +
                "left join department " +
                "on " +
                "class.departmentId = department.id " +
                "where 1=1 ");
        Page<LeaveApplicationInfo> pagination = PageHelper.startPage(page, limit);

        try {
            if (null != status) sb.append(" and leave_application.status = ").append(status);
            if (null != departmentId) {
                sb.append(" and department.id = ").append(departmentId);
            } else if (null != userId) {
                sb.append(" and t_user.id = ").append(userId);
            }

            return jdbcTemplateSupport.queryForPage(
                    sb.toString(),
                    pagination,
                    new BeanPropertyRowMapper<>(LeaveApplicationInfo.class));
        } catch (DataAccessException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public Integer getStatusById(int id) {
        String sql = "select status from leave_application where id = ?";
        Object[] params = {id};
        try {
            return jdbcTemplate.queryForObject(sql, params, Integer.class);
        } catch (DataAccessException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public boolean updateStatus(int id, int status) {
        String sql = "update leave_application set status = ? where id = ?";
        Object[] params = {
                status,
                id
        };
        try {
            jdbcTemplate.update(sql, params);
            return true;
        } catch (DataAccessException e) {
            e.printStackTrace();
        }
        return false;
    }

    @Override
    public void addDetails(int applicationId, long teacherId,int status, int result, String opinion) {
        String sql = "insert into leave_application_detail values (null,?,?,?,?,?)";
        Object[] params = {
                applicationId,
                teacherId,
                status,
                result,
                opinion
        };

        try {
            jdbcTemplate.update(sql, params);
        } catch (DataAccessException e) {
            e.printStackTrace();
        }
    }

    @Override
    public List<LeaveApplicationDetail> findDetailByApplicationId(Integer applicationId) {
        String sql = "SELECT \n" +
                "\tleave_application_detail.id,\n" +
                "   leave_application_detail.applicationId,\n" +
                "\t leave_application_status.`status`,\n" +
                "\t t_user.`name` username,\n" +
                "\t leave_application_detail.opinion,\n" +
                "\t leave_application_detail.result\n" +
                "FROM \n" +
                "leave_application_detail,\n" +
                "t_user,\n" +
                "leave_application,\n" +
                "leave_application_status\n" +
                "where\n" +
                "1=1\n" +
                "and\n" +
                "leave_application_detail.applicationId=leave_application.id\n" +
                "and\n" +
                "leave_application_detail.`status`=leave_application_status.id\n" +
                "and\n" +
                "leave_application_detail.teacherId=t_user.id\n" +
                "and\n" +
                "leave_application.id = ?";

        Object[] params = {applicationId};
        try {
            return jdbcTemplate.query(sql,params,new BeanPropertyRowMapper<>(LeaveApplicationDetail.class));
        } catch (DataAccessException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public List<LeaveApplication> findApplicationByStudentId(Integer id) {
        String  sql = "select * from leave_application where studentId = ?  order by id desc";
        try {
            return jdbcTemplate.query(sql,new Object[]{id},new BeanPropertyRowMapper<>(LeaveApplication.class));
        } catch (DataAccessException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public List<LeaveApplication> findBacklogApplication(Integer id) {
        String sql = "select * from leave_application where status in (2,4) and studentId = ?  order by id desc";
        try {
            return jdbcTemplate.query(sql,new Object[]{id},new BeanPropertyRowMapper<>(LeaveApplication.class));
        } catch (DataAccessException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public Map<String, Object> findApplicationInfoById(int id) {
        String sql = "select * from leave_application where id  = ?";
        try {
            return jdbcTemplate.queryForMap(sql,new Object[]{id});
        } catch (DataAccessException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public boolean studentBack(Integer applicationId) {
        String sql = "update leave_application set status = 3 where id = ?";
        try {
            jdbcTemplate.update(sql,new Object[]{applicationId});
            return true;
        } catch (DataAccessException e) {
            e.printStackTrace();
        }
        return false;
    }

}
